SQL QUICK REFERENCE


ColdFusion Query Syntax
--------------------------------------
<cfquery name="query_name" datasource=#datasource_name#>
  SELECT column_name
  FROM table_name
  WHERE column_name = #column_value#
</cfquery>


Select Statement
--------------------------------------
SELECT "column_name" FROM "table_name"


Distinct
--------------------------------------
SELECT DISTINCT "column_name"
FROM "table_name"


Where
--------------------------------------
SELECT "column_name"
FROM "table_name"
WHERE "condition"


And/Or
--------------------------------------
SELECT "column_name"
FROM "table_name"
WHERE "simple condition"
{[AND|OR] "simple condition"}+


In
--------------------------------------
SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ('value1', 'value2', ...)


Between
--------------------------------------
SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2'


Like
--------------------------------------
SELECT "column_name"
FROM "table_name"
WHERE "column_name" LIKE {PATTERN}


Order By
--------------------------------------
SELECT "column_name"
FROM "table_name"
[WHERE "condition"]
ORDER BY "column_name" [ASC, DESC]


Count
--------------------------------------
SELECT COUNT("column_name")
FROM "table_name"


Group By
--------------------------------------
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"


Having
--------------------------------------
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
HAVING (arithematic function condition)


Create Table Statement
--------------------------------------
CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... )


Drop Table Statement
--------------------------------------
DROP TABLE "table_name"


Truncate Table Statement
--------------------------------------
TRUNCATE TABLE "table_name"


Insert Into Statement
--------------------------------------
INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)


Update Statement
--------------------------------------
UPDATE "table_name"
SET "column_1" = [new value]
WHERE {condition}


Delete From Statement
--------------------------------------
DELETE FROM "table_name"
WHERE {condition}


OperatorS
--------------------------------------
= Equal 
<> Not Equal 
> Greater Than 
< Less Than 
>= Greater Than Or Equal 
<= Less Than Or Equal


Functions
--------------------------------------
SUM - Total of the values in a field. 
AVG - Average of the values in a field. 
MIN - Lowest value in a field. 
MAX - Highest value in a field. 
COUNT - Number of values in a field, not counting Null (blank) values.


ColdFusion Query Syntax with Function
--------------------------------------
<cfquery name="query_name" datasource=#datasource_name#>
  SELECT MAX(column_name)
  FROM table_name
  WHERE column_name = #column_value#
</cfquery>




Data Types
--------------------------------------
bigint:  	Integer data from -2^63 through 2^63-1  
int:  		Integer data from -2^31 through 2^31 - 1  
smallint:  	Integer data from -2^15 through 2^15 - 1  
tinyint:  	Integer data from 0 through 255  
bit:  		Integer data with either a 1 or 0 value  
decimal:  	Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1  
numeric:  	Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1  
money:  	Monetary data values from -2^63 through 2^63 - 1  
smallmoney:  	Monetary data values from -214,748.3648 through +214,748.3647  
float:  	Floating precision number data from -1.79E + 308 through 1.79E + 308  
real:  		Floating precision number data from -3.40E + 38 through 3.40E + 38  
datetime:  	Date and time data from January 1, 1753, through December 31, 9999,
		with an accuracy of 3.33 milliseconds  
smalldatetime:  Date and time data from January 1, 1900, through June 6, 2079,
		with an accuracy of one minute  
char:  		Fixed-length character data with a maximum length of 8,000 characters  
varchar:  	Variable-length data with a maximum of 8,000 characters  
text:  		Variable-length data with a maximum length of 2^31 - 1 characters  
nchar:  	Fixed-length Unicode data with a maximum length of 4,000 characters  
nvarchar:  	Variable-length Unicode data with a maximum length of 4,000 characters  
ntext:  	Variable-length Unicode data with a maximum length of 2^30 - 1 characters  
binary:  	Fixed-length binary data with a maximum length of 8,000 bytes  
varbinary:  	Variable-length binary data with a maximum length of 8,000 bytes  
image:  	Variable-length binary data with a maximum length of 2^31 - 1 bytes  
cursor:  	A reference to a cursor  
sql_variant:  	A data type that stores values of various data types,
		except text, ntext, timestamp, and sql_variant  
table:  	A special data type used to store a result set for later processing  
timestamp:  	A database-wide unique number that gets updated every time
		a row gets updated  
uniqueidentifier:  A globally unique identifier  

